
USE Lib
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------

--Enable CLR Integration if is not enabled
IF NOT EXISTS (SELECT * FROM sys.configurations WHERE [name] = 'show advanced options' AND [value] = 1) BEGIN 
	EXEC sp_configure 'show advanced options', 1;
	RECONFIGURE;
	PRINT '<<< Enable SQL Advanced Options - Completed with SUCCESS >>>'
END

IF NOT EXISTS (SELECT * FROM sys.configurations WHERE [name] = 'clr enabled' AND [value] = 1) BEGIN 
	EXEC sp_configure 'clr enabled', 1
	RECONFIGURE
	PRINT '<<< Enable CLR - Completed with SUCCESS >>>'
END

-- Drop existing functions so assembly can be dropped
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_compress' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	DROP FUNCTION [clr].fn_compress
	PRINT '<<< DROP FUNCTION [clr].[fn_compress] - Completed with SUCCESS >>>'
END

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_decompress' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	DROP FUNCTION [clr].fn_decompress
	PRINT '<<< DROP FUNCTION [clr].[fn_decompress] - Completed with SUCCESS >>>'
END


--Drop the assembly if it exist
IF EXISTS (SELECT * FROM sys.assemblies asms WHERE asms.name = N'ClrCompression') BEGIN 
	DROP ASSEMBLY [ClrCompression]
	PRINT '<<< DROP Assembly ClrCompression - Completed with SUCCESS >>>'
END

-- Create the assembly if it does not exists
CREATE ASSEMBLY [ClrCompression] 
FROM 'C:\Program Files\Microsoft SQL Server\100\CLR\ClrCompression.dll' 
-- Testing only
--FROM 'D:\Projects\SqlLib\Project\CLR\Compression\bin\Debug\ClrCompression.dll'	
WITH PERMISSION_SET = SAFE
PRINT '<<< CREATE Assembly ClrCompression - Completed with SUCCESS >>>'


GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


/*================================================================================================
Name:			[clr].[fn_compress]
Type:			Custom aggregate 
Owner:					
Description:	Compression  
Parameters:		@blob 
Dependens on:  	ClrCompression.dll
Usage:			SELECT clr.fn_compress([blob_column])	
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_compress&referringTitle=Documentation		
Version:		20121127
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		11/27/2012 - Adrian E Dudau - Function created
================================================================================================*/
--Create the functions
CREATE FUNCTION clr.[fn_compress] (@blob VARBINARY(MAX))
RETURNS varbinary(MAX) AS 
EXTERNAL NAME [ClrCompression].[CompressionTools].[Compress];

GO 

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_compress' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	PRINT '<<< CREATE FUNCTION [clr].[fn_compress] - Completed with SUCCESS >>>'
END
ELSE BEGIN 
	PRINT '<<< CREATE FUNCTION [clr].[fn_compress] - Completed with ERROR >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------




/*================================================================================================
Name:			[clr].[fn_decompress]
Type:			Custom aggregate 
Owner:					
Description:	blob decompression  
Parameters:		@blob 
Dependens on:  	ClrCompression.dll
Usage:			SELECT clr.fn_decompress([blob_compressed_column])
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=fn_decompress&referringTitle=Documentation		
Version:		20121127
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
--------------------------------------------------------------------------------------------------
History:		11/27/2012 - Adrian E Dudau - Function created
================================================================================================*/
CREATE FUNCTION clr.[fn_decompress] (@blob VARBINARY(MAX))
RETURNS VARBINARY(MAX) AS 
EXTERNAL NAME [ClrCompression].[CompressionTools].[Decompress];

GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'clr' AND ROUTINE_NAME = 'fn_decompress' AND ROUTINE_TYPE = 'FUNCTION') BEGIN
	PRINT '<<< CREATE FUNCTION [clr].[fn_decompress] - Completed with SUCCESS >>>'
END

ELSE BEGIN 
	PRINT '<<< CREATE FUNCTION [clr].[fn_decompress] - Completed with ERROR >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------


